﻿#include "datasum.h"
#include "ui_datasum.h"

DataSum::DataSum(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::DataSum)
{
    ui->setupUi(this);
    //禁止窗口最大化按钮
    setWindowFlags(windowFlags()&~Qt::WindowMaximizeButtonHint);
    setFixedSize(this->width(),this->height());

    TableWidgetInit();
}

DataSum::~DataSum()
{
    delete ui;
}


void DataSum::on_sumdataButton_clicked()
{
    int i=0;
    QSqlQuery sqlquery;
    sqlquery.exec("select *from commoditydatatable");

    while(sqlquery.next())
    {
        QString StrId=sqlquery.value(0).toString();
        QString StrName=sqlquery.value(1).toString();
        QString StrAmount=sqlquery.value(2).toString();
        QString StrUnitPrice=sqlquery.value(3).toString();

        // 将商品数量 * 商品单价 = 商品总价
        double dSum=StrAmount.toDouble()*StrUnitPrice.toDouble();
        QString StrSum=QString("%1").arg(dSum);

        QString StrSupplier=sqlquery.value(4).toString();
        QString StrDirector=sqlquery.value(5).toString();
        QString StrWarehousingTime=sqlquery.value(6).toString();
        QString StrDeliveryTime=sqlquery.value(7).toString();
        QString StrRemarks=sqlquery.value(8).toString();

        ui->ListData->setItem(i,0,new QTableWidgetItem(StrId));
        ui->ListData->setItem(i,1,new QTableWidgetItem(StrName));
        ui->ListData->setItem(i,2,new QTableWidgetItem(StrAmount));
        ui->ListData->setItem(i,3,new QTableWidgetItem(StrUnitPrice));

        // 显示商品总价
        ui->ListData->setItem(i,4,new QTableWidgetItem(StrSum));

        ui->ListData->setItem(i,5,new QTableWidgetItem(StrSupplier));
        ui->ListData->setItem(i,6,new QTableWidgetItem(StrDirector));
        ui->ListData->setItem(i,7,new QTableWidgetItem(StrWarehousingTime));
        ui->ListData->setItem(i,8,new QTableWidgetItem(StrDeliveryTime));
        ui->ListData->setItem(i,9,new QTableWidgetItem(StrRemarks));

        i++;
    }

}



void DataSum::on_SaveButton_clicked()
{
    // 保存文件扩展名为.xls
    QDateTime time;
    QString strTemp;

    time=QDateTime::currentDateTime();//获取系统时间作为文件名
    strTemp=time.toString("yyyy-MM-dd-hhmmss");

    //QMessageBox::information(this,"测试",strTemp);
    QString strfilename=QFileDialog::getSaveFileName(this,tr("Excel Files"),QString("%1%2.xls").arg(strTemp,"-库存总价信息"),tr("Excel Files(*.xls)"));
    //QMessageBox::information(this,"测试",strfilename);
    if(!strfilename.isEmpty())
    {
        QAxObject *Excel=new QAxObject;
        if(Excel->setControl("Excel.Application"))
        {
            Excel->dynamicCall("SetVisible (bool Visible)",false);
            Excel->setProperty("DisplayAlerts",false);

            QAxObject *workbooks=Excel->querySubObject("WorkBooks");
            workbooks->dynamicCall("Add");
            QAxObject *workbook=Excel->querySubObject("ActiveWorkBook");
            QAxObject *worksheet=workbook->querySubObject("Worksheets(int)",1);
            QAxObject *cell;
            for(int i=1;i<=ui->ListData->columnCount();i++)
            {
                cell=worksheet->querySubObject("Cells(int,int)",1,i);
                cell->setProperty("RowHeight",38);
                cell->dynamicCall("SetValue(const QString&)",ui->ListData->horizontalHeaderItem(i-1)->data(0).toString());
            }
            for(int i=2;i<=ui->ListData->rowCount()+1;i++)
            {
                for(int j=1;j<=ui->ListData->columnCount();j++)
                {
                    /*cell=worksheet->querySubObject("Cells(int,int)",i,j);
                    cell->dynamicCall("SetValue(const QString&)",ui->ListData->item(i-2,j-1)->text()+"\t");*/
                    QTableWidgetItem *item = ui->ListData->item(i - 2, j - 1);
                    QString text = item ? item->text() + "\t" : "";
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)", i, j);
                    cell->dynamicCall("SetValue(const QString&)", text);
                }
            }

            workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(strfilename));
            workbook->dynamicCall("Close()");
            Excel->dynamicCall("Quit()");
            delete Excel;
            Excel=NULL;
        }
    }
}

void DataSum::TableWidgetInit()
{
    ui->ListData->setColumnCount(10);
    ui->ListData->setRowCount(200);
    ui->ListData->setHorizontalHeaderLabels
        (QStringList()<<"商品编号"<<"名称"<<"数量"<<"单价"<<"总价"<<"供应商家"<<"负责人"<<"入库时间"<<"出库时间"<<"备注");

    ui->ListData->setColumnWidth(0,80);
    ui->ListData->setColumnWidth(1,150);
    ui->ListData->setColumnWidth(2,80);
    ui->ListData->setColumnWidth(3,80);
    ui->ListData->setColumnWidth(4,80);
    ui->ListData->setColumnWidth(5,300);
    ui->ListData->setColumnWidth(6,80);
    ui->ListData->setColumnWidth(7,250);
    ui->ListData->setColumnWidth(8,250);
    ui->ListData->setColumnWidth(9,180);
}
